#根据books表的作者，获取人物基本表中的人物id和朝代id
import re

import KBase
import pymysql

def getPersonidAnddyid():
    sql="select id,`书名`,`作者`,`简介` from books where  `国籍`='中国' and `作者` is not null and `作者`!='' and c_dy is null group by book_id order by id"
    cur1.execute(sql)
    datas=cur1.fetchall()
    # sql2="select c_dynasty_chn from `朝代_简_copy`"
    # cur2.execute(sql2)
    # dynasty=cur2.fetchall()
    # dys=[]
    # for dy in dynasty:
    #     dys.append(dy[0])
    # print(dys)
    ischinese = re.compile(u'[\u4e00-\u9fa5]')  #检查中文
    values=[]
    for data in datas:
        id=data[0]
        name=data[1]
        author=data[2]
        context=data[3]
        match=ischinese.search(author)
        if match:
            authors=author.strip().replace('  ',' ').split(' ')
            if len(authors)>1:
                personids=[]
                dyid=None
                for author in authors:
                    #person=authors[0]
                    sql1="select c_personid,c_dy,c_name_chn from `人物基本表_new` where c_name_chn='"+author+"'"
                    cur2.execute(sql1)
                    persons=cur2.fetchall()
                    if persons:
                        personinfo=persons[0]
                        personid=personinfo[0]
                        dy=personinfo[1]
                        if dy:
                            if personid:
                                dyid=int(dy)
                                personids.append(str(personid))

                            else:
                                dyid=int(dy)
                                personids.append('None')

                        else:
                            if personid:
                                personids.append(str(personid))
                            else:
                                personids.append('None')
                        # cur1.execute(update)
                        # conn1.commit()
                    else:
                        personids.append('None')

                ids=';'.join(personids)
                if dyid:
                    update="update books set authorgone=1,c_person_id='"+ids+"',c_dy="+str(dyid)+" where id="+str(id)
                else:
                    update="update books set authorgone=1,c_person_id='"+ids+"' where id="+str(id)

                print(update)
                cur1.execute(update)
                conn1.commit()

def adjustdy():
    sql="select id,`作者` from books where c_dy is not null and c_dy!=6 and `时期标志`=700"
    cur1.execute(sql)
    datas=cur1.fetchall()
    for data in datas:
        print(data)
        id=data[0]
        author=data[1]
        sql1="select c_personid,c_dy,c_name_chn from `人物基本表_new` where c_name_chn='"+author+"'"
        cur2.execute(sql1)
        persons=cur2.fetchall()
        flag=False
        for person in persons:
            if flag==False:
                personid=data[0]
                dy=person[1]
                if dy:
                    if int(dy)==6:
                        flag=True
                        if personid:
                            update="update books set c_person_id="+str(personid)+",c_dy="+str(int(dy))+" where id="+str(id)
                        else:
                            update="update books set c_dy="+str(int(dy))+" where id="+str(id)
                        print(update)
                        cur1.execute(update)
                        conn1.commit()
                    elif int(dy)==7:
                        flag=True
                        if personid:
                            update="update books set c_person_id="+str(personid)+",c_dy="+str(int(dy))+" where id="+str(id)
                        else:
                            update="update books set c_dy="+str(int(dy))+" where id="+str(id)
                        print(update)
                        cur1.execute(update)
                        conn1.commit()

def getHFSID():
    sql="select * from books "
    cur1.execute(sql)
    datas=cur1.fetchall()
    values=[]
    for data in datas:
        id=data[0]
        book_id=data[15]
        index=data[16]
        if index:
            hfsid=book_id+"_"+str(index)
        elif index==0:
            hfsid=book_id+"_"+str(index)
        else:
            hfsid=book_id+"_"+"null"

        value=(hfsid,id)
        values.append(value)
        #update="update books set hfsid='"+hfsid+"' where id="+str(id)
        if len(values)>10000:
            update ="update books set hfsid=%s where id=%s"
            cur1.executemany(update,values)
            conn1.commit()
            values=[]
    update ="update books set hfsid=%s where id=%s"
    cur1.executemany(update,values)
    conn1.commit()

if __name__ == '__main__':
    try:
        conn1 = pymysql.Connect(host = '10.170.128.56',
                                port = 3306,
                                user = 'root',
                                passwd = '123456',
                                db = 'books',
                                charset='utf8mb4')
        cur1 = conn1.cursor()
        conn2 = pymysql.Connect(host = '10.170.128.56',
                                port = 3306,
                                user = 'root',
                                passwd = '123456',
                                db = 'cnki_humantity_simple',
                                charset='utf8mb4')
        cur2 = conn2.cursor()

        getHFSID()

    finally:
        cur1.close()
        conn1.close()
        cur2.close()
        conn2.close()
